﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessInstockGroup
// 文件功能描述：零星物品入库数据访问层
//
// 
// 创建标识：2012-01-14 王冲
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessInstockSundries
    {

        /// <summary>
        /// 保存零星物品入库
        /// </summary>
        /// <param name="igpara"></param>
        /// <returns></returns>
        public int SaveSundries(object[] igpara)
        {
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(GetConntionString.conntionstring(), "Proc_ConInstockDetailedSporadic", igpara);
        }

        /// <summary>
        /// 获取一条零星物品入库信息
        /// </summary>
        /// <param name="ID">主键</param>
        /// <returns>零星物品入库信息</returns>
        public DataRow GetInstockGroup(int ID)
        {
            string query = "select ID,InstockDt,Goods_ID,Count,UserMan,Remark,RecordDateTime,OperatorID,OrderNo "
                     + "from Con_Instock_Detailed_Sporadic where ID = " + ID.ToString();
            sqldb db = new sqldb();
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query).Tables[0].Rows[0];
        }


        /// <summary>
        /// 获取零星物品入库信息(分页）
        /// </summary>
        /// <param name="pagesize">页大小</param>
        /// <param name="startindex">起始页</param>
        /// <param name="dict">查询条件</param>
        /// <returns>零星物品入库信息</returns>
        public DataSet GetInstockGroup(int pagesize, int startindex,
            string condition)
        {
            int startrow = pagesize * (startindex - 1);
            int endrow = pagesize * startindex + 1;
            StringBuilder str = new StringBuilder();
            str.Append("select ID,InstockDt,Goods_ID,Count,UserMan,Remark,RecordDateTime,OperatorID,OrderNo from (");
            str.Append("select ROW_NUMBER() OVER(ORDER BY ID DESC) rownumber,ID,InstockDt,Goods_ID,Count,UserMan,Remark,RecordDateTime,OperatorID,OrderNo ");
            str.Append("from Con_Instock_Detailed_Sporadic where 1=1 ");
            str.Append(condition);
            str.Append(") a ");
            str.Append("where a.rownumber > ");
            str.Append(startrow);
            str.Append(" and a.rownumber < ");
            str.Append(endrow);
            sqldb db = new sqldb();
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, str.ToString());
        }

        /// <summary>
        /// 获取零星物品入库信息(不分页）
        /// </summary>
        /// <param name="dict">查询条件</param>
        /// <returns>零星物品入库信息</returns>
        public DataSet GetInstockGroup(string condition)
        {
            StringBuilder str = new StringBuilder();
            str.Append("select ID,InstockDt,Goods_ID,Count,UserMan,Remark,RecordDateTime,OperatorID,OrderNo from ");
            str.Append("Con_Instock_Detailed_Sporadic where 1=1 ");
            str.Append(condition);
            sqldb db = new sqldb();
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, str.ToString());
        }

        /// <summary>
        /// 删除零星物品入库
        /// </summary>
        /// <param name="ID">主键</param>
        /// <returns></returns>
        public int DeleteInstockGroup(int id)
        {
            //int rowcount = 0;
            //using (SqlConnection sqlconn = new SqlConnection(GetConntionString.conntionstring()))
            //{
            //    sqlconn.Open();
            //    SqlTransaction sqltran = sqlconn.BeginTransaction();
            //    try
            //    {
            //        sqldb db = new sqldb();
            //        StringBuilder str = new StringBuilder();
            //        str.Append("delete from Con_Instock_Detailed_Sporadic where ID = " + id.ToString());
            //        rowcount = db.ExecuteNonQuery(sqltran, CommandType.Text, str.ToString());
            //        sqltran.Commit();
            //        sqlconn.Close();
            //    }
            //    catch (Exception ex)
            //    {
            //        sqltran.Rollback();
            //        sqlconn.Close();
            //        throw new Exception(ex.Message);
            //    }
            //};
            //return rowcount;
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                /*
                 *先根据零星物品食材入库编号查询出食材类型编号，然后再去主零星物品出库表里面查询一下有没有这个食材类型
                 *编号和这个单价的出库信息，如果有则不能删除直接返回0，如果没有则可以删除，返回删除影响行数。
                 */
                int i;
                sqldb db = new sqldb();
                string query = "select Goods_ID from Con_Instock_Detailed_Sporadic where ID='" + id + "'";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                query = "select count(*) from Con_Outstock_Detailed_Sporadic where GoodsID='" + ds.Tables[0].Rows[0]["Goods_ID"].ToString() + "'";
                ds = db.ExecuteDataset(conn, CommandType.Text, query);
                if (ds.Tables[0].Rows[0][0].ToString() == "0")
                {
                    query = "Delete from Con_Instock_Detailed_Sporadic where ID=" + id;
                    i = db.ExecuteNonQuery(conn, CommandType.Text, query);
                }
                else
                {
                    i = 0;
                }
                return i;
            }
        }

        /// <summary>
        /// 获取零星物品的数据
        /// </summary>
        /// <returns></returns>
        public int GetRowCount(string condition)
        {
            string query = "select count(1) from Con_Instock_Detailed_Sporadic Where 1=1 " + condition + "";
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }
        /// <summary>
        /// 获取批号
        /// </summary>
        /// <returns></returns>
        public string GetInstockSporadicOrderNo()
        {
            sqldb db = new sqldb();
            string query = "Execute GetCon_Sporadic_OrderNo";
            object strorderno = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return strorderno.ToString();
        }
    }
}
